package com.mall.search.mallsearch.util;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Date;


public class ExcelUtils {

    /**
     * 打开只读的工作簿
     *
     * @param filePath
     * @return
     * @throws RuntimeException
     */
    public static XSSFWorkbook openXSSFWorkbook(String filePath) {
        try {
            InputStream is = new FileInputStream(filePath);
            return new XSSFWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 打开只读的工作簿
     *
     * @param is
     * @return
     * @throws RuntimeException
     */
    public static XSSFWorkbook openXSSFWorkbook(InputStream is) {
        try {
            return new XSSFWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static HSSFWorkbook openHSSFWorkbook(String filePath) {
        try {
            InputStream is = new FileInputStream(filePath);
            return new HSSFWorkbook(is);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 打开可写的工作簿
     *
     * @param outputStream
     * @return
     * @throws RuntimeException
     */
    public static WritableWorkbook openWritableWorkbook(
            OutputStream outputStream) throws RuntimeException {
        try {
            return Workbook.createWorkbook(outputStream);
        } catch (Exception ex) {
            throw new RuntimeException("打开可写的工作簿失败!", ex);
        }
    }

    /**
     * 打开可写的工作簿
     *
     * @param filePath
     * @return
     * @throws RuntimeException
     */
    public static WritableWorkbook openWritableWorkbook(String filePath)
            throws RuntimeException {
        try {
            return Workbook.createWorkbook(new File(filePath));
        } catch (Exception ex) {
            throw new RuntimeException("打开可写的工作簿失败!", ex);
        }
    }

    /**
     * 打开可写的工作簿，将只读的工作簿中的内容填充到可写的工作簿
     *
     * @param filePath
     * @param wb
     * @return
     * @throws RuntimeException
     */
    public static WritableWorkbook openWritableWorkbook(String filePath,
                                                        Workbook wb) throws RuntimeException {
        try {
            return Workbook.createWorkbook(new File(filePath), wb);
        } catch (Exception ex) {
            throw new RuntimeException("打开可写的工作簿失败!", ex);
        }
    }

    /**
     * 打开可写的工作簿，将只读的工作簿中的内容填充到可写的工作簿
     *
     * @param outputStream
     * @param wb
     * @return
     * @throws RuntimeException
     */
    public static WritableWorkbook openWritableWorkbook(
            OutputStream outputStream, Workbook wb) throws RuntimeException {
        try {
            return Workbook.createWorkbook(outputStream, wb);
        } catch (Exception ex) {
            throw new RuntimeException("打开可写的工作簿失败!", ex);
        }
    }

    /**
     * 获取只读的Sheet
     *
     * @param wb
     * @param index
     * @return
     */
    public static Sheet getReadSheet(Workbook wb, int index) {
        return wb.getSheet(index);
    }

    /**
     * 获取只读的Sheet
     *
     * @param wb
     * @param sheetName
     * @return
     */
    public static Sheet getReadSheet(Workbook wb, String sheetName) {
        return wb.getSheet(sheetName);
    }

    /**
     * 获取可写的Sheet
     *
     * @param ww
     * @param index
     * @return
     * @throws RuntimeException
     */
    public static WritableSheet getWritableSheet(WritableWorkbook ww, int index)
            throws RuntimeException {
        if (index >= ww.getSheets().length) {
            return ww.createSheet("Sheet" + (index + 1), index);
        }

        return ww.getSheet(index);
    }

    /**
     * 获取可写的Sheet，并设置sheet名称
     *
     * @param ww
     * @param index
     * @param sheetName
     * @return
     * @throws RuntimeException
     */
    public static WritableSheet getWritableSheet(WritableWorkbook ww,
                                                 int index, String sheetName) throws RuntimeException {
        if (index >= ww.getSheets().length) {
            return ww.createSheet(sheetName, index);
        }

        WritableSheet ws = ww.getSheet(index);
        ws.setName(sheetName);
        return ws;
    }

    /**
     * 获取只读的Cell
     *
     * @param sheet
     * @param col
     * @param row
     * @return
     */
    public static Cell getReadCell(Sheet sheet, int col, int row) {
        return sheet.getCell(col, row);
    }

    /**
     * 获取可写的Cell
     *
     * @param ws
     * @param col
     * @param row
     * @return
     */
    public static WritableCell getWritableCell(WritableSheet ws, int col,
                                               int row) {
        return ws.getWritableCell(col, row);
    }

    /**
     * 设置单元格的值
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    String value) throws RuntimeException {
        try {
            jxl.write.Label label = new jxl.write.Label(col, row, value);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置单元格的值，并设置单元格格式
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @param wcf
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    String value, WritableCellFormat wcf) throws RuntimeException {
        try {
            jxl.write.Label label = new jxl.write.Label(col, row, value, wcf);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置单元格的值
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    Double value) throws RuntimeException {
        try {
            jxl.write.Number label = new jxl.write.Number(col, row, value);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置单元格的值，并设置单元格格式
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @param wcf
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    Double value, WritableCellFormat wcf) throws RuntimeException {
        try {
            jxl.write.Number label = new jxl.write.Number(col, row, value, wcf);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置单元格的值
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    Date value) throws RuntimeException {
        try {
            jxl.write.DateTime label = new jxl.write.DateTime(col, row, value);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置单元格的值，并设置单元格格式
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @param wcf
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    Date value, WritableCellFormat wcf) throws RuntimeException {
        try {
            jxl.write.DateTime label = new jxl.write.DateTime(col, row, value,
                    wcf);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置单元格的值
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    boolean value) throws RuntimeException {
        try {
            jxl.write.Boolean label = new jxl.write.Boolean(col, row, value);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置单元格的值，并设置单元格格式
     *
     * @param ws
     * @param col
     * @param row
     * @param value
     * @param wcf
     * @throws RuntimeException
     */
    public static void setCellValue(WritableSheet ws, int col, int row,
                                    boolean value, WritableCellFormat wcf) throws RuntimeException {
        try {
            jxl.write.Boolean label = new jxl.write.Boolean(col, row, value,
                    wcf);
            ws.addCell(label);
        } catch (Exception ex) {
            throw new RuntimeException("设置单元格的值失败!", ex);
        }
    }

    /**
     * 设置行高
     *
     * @param ws
     * @param row
     * @param height
     * @throws RuntimeException
     */
    public static void setRowHeight(WritableSheet ws, int row, int height)
            throws RuntimeException {
        try {
            ws.setRowView(row, height);
        } catch (Exception ex) {
            throw new RuntimeException("设置行高失败!", ex);
        }
    }

    /**
     * 设置列宽
     *
     * @param ws
     * @param col
     * @param width
     * @throws RuntimeException
     */
    public static void setColumnWidth(WritableSheet ws, int col, int width)
            throws RuntimeException {
        try {
            ws.setColumnView(col, width);
        } catch (Exception ex) {
            throw new RuntimeException("设置列宽失败!", ex);
        }
    }

    /**
     * 合并单元格
     *
     * @param ws
     * @param colStart
     * @param rowStart
     * @param colEnd
     * @param rowEnd
     * @throws RuntimeException
     */
    public static void mergeCells(WritableSheet ws, int colStart, int rowStart,
                                  int colEnd, int rowEnd) throws RuntimeException {
        try {
            ws.mergeCells(colStart, rowStart, colEnd, rowEnd);
        } catch (Exception ex) {
            throw new RuntimeException("合并单元格失败!", ex);
        }
    }

    /**
     * 关闭可写的工作簿
     *
     * @param ww
     * @throws RuntimeException
     */
    public static void closeWritableWorkbook(WritableWorkbook ww)
            throws RuntimeException {
        try {
            ww.write();
            ww.close();
            ww = null;
        } catch (Exception ex) {
            throw new RuntimeException("关闭可写的工作簿失败!", ex);
        }
    }

    /**
     * 关闭只读的工作簿
     *
     * @param wb
     * @throws RuntimeException
     */
    public static void closeWorkbook(XSSFWorkbook wb) throws RuntimeException {
        try {
            wb = null;
        } catch (Exception ex) {
            throw new RuntimeException("关闭只读的工作簿失败!", ex);
        }
    }


}
